package cn.edu.sanxiau.www.dao.impl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.sanxiau.www.dao.PermissionDao;
import cn.edu.sanxiau.www.model.Permission;
import cn.edu.sanxiau.www.model.Role;
import frame.DBUtils.JdbcUtils;

public class PermissionDaoImpl implements PermissionDao {

    @Override
    public List<Permission> queryAllPermission() {
        //操作数据库
        //1.连接对象
        Connection conn = null;
        //2.预处理对象
        PreparedStatement pstmt = null;
        //3.结果集
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM permission";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            //对象列表
            List<Permission> permissionList_db = new ArrayList<Permission>();

            while (rs.next()) {
                Permission permission_db = new Permission();
                permission_db.setPermission_id(rs.getInt("permission_id"));
                permission_db.setP_id(rs.getInt("p_id"));
                permission_db.setName(rs.getString("name"));
                permission_db.setType(rs.getString("type"));
                permission_db.setUrl(rs.getString("url"));
                permission_db.setPermission_code(rs.getString("permission_code"));
                permission_db.setState(rs.getString("state"));
                permission_db.setIcon(rs.getString("icon"));

                //放每个对象
                permissionList_db.add(permission_db);
            }
            return permissionList_db;

        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int addPermissionByPermission(Permission permission) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "INSERT INTO  permission (permission_id,p_id,name,type,url,permission_code,state,icon) VALUES(?,?,?,?,?,?,?,?)";
//	            Statement
//	            prepareStatement
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, permission.getPermission_id());
            pstmt.setInt(2, permission.getP_id());
            pstmt.setString(3, permission.getName());
            pstmt.setString(4, permission.getType());
            pstmt.setString(5, permission.getUrl());
            pstmt.setString(6, permission.getPermission_code());
            pstmt.setString(7, permission.getState());
            pstmt.setString(8, permission.getIcon());


            int n = pstmt.executeUpdate();
            return n;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public int deletePermissionByPermission_id(int permission_id) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = JdbcUtils.getConnection();

            String sql = "DELETE FROM permission WHERE permission_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, permission_id);

            int m = pstmt.executeUpdate();
            return m;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public Permission queryPermissionByPermission_id(int permission_id) {
        //操作数据库
        //1.连接对象
        Connection conn = null;
        //2.预处理对象
        PreparedStatement pstmt = null;
        //3.结果集
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM permission WHERE permission_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, permission_id);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Permission permission_db = new Permission();
                permission_db.setPermission_id(rs.getInt("permission_id"));
                permission_db.setP_id(rs.getInt("p_id"));
                permission_db.setName(rs.getString("name"));
                permission_db.setType(rs.getString("type"));
                permission_db.setUrl(rs.getString("url"));
                permission_db.setPermission_code(rs.getString("permission_code"));
                permission_db.setState(rs.getString("state"));
                permission_db.setIcon(rs.getString("icon"));
                return permission_db;

            }
        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int updatePermissionByPermission(Permission permission) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = JdbcUtils.getConnection();
            String sql = "UPDATE permission SET p_id=?,name=?,type=?,url=?,permission_code=?,state=?,icon=? WHERE permission_id=?";
            pstmt = conn.prepareStatement(sql);

            pstmt.setInt(1, permission.getP_id());
            pstmt.setString(2, permission.getName());
            pstmt.setString(3, permission.getType());
            pstmt.setString(4, permission.getUrl());
            pstmt.setString(5, permission.getPermission_code());
            pstmt.setString(6, permission.getState());
            pstmt.setString(7, permission.getIcon());
            pstmt.setInt(8, permission.getPermission_id());

            int n = pstmt.executeUpdate();
            return n;

        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Permission> queryXZPermissionByRole_id(int role_id) {
        //操作数据库
        //1.连接对象
        Connection conn = null;
        //2.预处理对象
        PreparedStatement pstmt = null;
        //3.结果集
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT permission.permission_id,permission.p_id,permission.`name`,permission.type,permission.url,permission.permission_code,permission.state,permission.icon FROM rolepermission INNER JOIN permission ON rolepermission.permission_id = permission.permission_id WHERE role_id =?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, role_id);
            rs = pstmt.executeQuery();

            //对象列表
            List<Permission> permissionList_db = new ArrayList<Permission>();

            while (rs.next()) {
                Permission permission_db = new Permission();
                permission_db.setPermission_id(rs.getInt("permission_id"));
                permission_db.setP_id(rs.getInt("p_id"));
                permission_db.setName(rs.getString("name"));
                permission_db.setType(rs.getString("type"));
                permission_db.setUrl(rs.getString("url"));
                permission_db.setPermission_code(rs.getString("permission_code"));
                permission_db.setState(rs.getString("state"));
                permission_db.setIcon(rs.getString("icon"));

                //放每个对象
                permissionList_db.add(permission_db);
            }
            return permissionList_db;

        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Permission> queryUser_xz_permissionByUser_id(int user_id) {
        //操作数据库
        //1.连接对象
        Connection conn = null;
        //2.预处理对象
        PreparedStatement pstmt = null;
        //3.结果集
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT permission.permission_id,permission.p_id,permission.`name`,permission.type,permission.url,permission.permission_code,permission.state,permission.icon FROM userrole INNER JOIN role ON userrole.role_id = role.role_id INNER JOIN rolepermission ON role.role_id = rolepermission.role_id INNER JOIN permission ON rolepermission.permission_id = permission.permission_id WHERE user_id=? and type='菜单'";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, user_id);
            rs = pstmt.executeQuery();

            //对象列表
            List<Permission> permissionList_db = new ArrayList<Permission>();

            while (rs.next()) {
                Permission permission_db = new Permission();
                permission_db.setPermission_id(rs.getInt("permission_id"));
                permission_db.setP_id(rs.getInt("p_id"));
                permission_db.setName(rs.getString("name"));
                permission_db.setType(rs.getString("type"));
                permission_db.setUrl(rs.getString("url"));
                permission_db.setPermission_code(rs.getString("permission_code"));
                permission_db.setState(rs.getString("state"));
                permission_db.setIcon(rs.getString("icon"));

                //放每个对象
                permissionList_db.add(permission_db);
            }
            return permissionList_db;

        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Permission> queryXZPermissionAllSonByP_id(int p_id) {
        //操作数据库
        //1.连接对象
        Connection conn = null;
        //2.预处理对象
        PreparedStatement pstmt = null;
        //3.结果集
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM permission  WHERE p_id =?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, p_id);
            rs = pstmt.executeQuery();

            //对象列表
            List<Permission> permissionList_db = new ArrayList<Permission>();

            while (rs.next()) {
                Permission permission_db = new Permission();
                permission_db.setPermission_id(rs.getInt("permission_id"));
                permission_db.setP_id(rs.getInt("p_id"));
                permission_db.setName(rs.getString("name"));
                permission_db.setType(rs.getString("type"));
                permission_db.setUrl(rs.getString("url"));
                permission_db.setPermission_code(rs.getString("permission_code"));
                permission_db.setState(rs.getString("state"));
                permission_db.setIcon(rs.getString("icon"));

                //放每个对象
                permissionList_db.add(permission_db);
            }
            return permissionList_db;

        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

}
